using System;
using System.Collections;
using System.Globalization;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using System.Drawing;
using System.ComponentModel;
using System.Reflection;
using System.Data.SQLite;
using System.Data;
using System.IO;
using sharpCash.ReflectionClasses;
using sharpCash.OFXClasses;
using sharpCash.SQLwrapper;

namespace sharpCash
{
    /// <summary>
    /// Version of this code - for use later
    /// </summary>
    public class Version
    {
        private string version = "0.4.2";
        public string DBVersion
        {
            get { return version; }
        }
    }
    /// <summary>
    /// Stores the column attributes settings
    /// </summary>
    public class Settings
    {
        private Hashtable settings = new Hashtable();
        private string settingsString;
        private Serializer phpSer = new Serializer();
        private string ID = null;

        public string SettingsString
        {
            get { return Convert.ToBase64String(Encoding.ASCII.GetBytes(settingsString)); }
            set { settingsString = value; }
        }
        public Settings()
        {            
        }
        public string getID()
        {
            return ID;
        }
        public void setID(string ID)
        {
            this.ID = ID;
        }
        public Hashtable getSettings()
        {
            return settings;
        }
        public void setSettings(Hashtable tmp)
        {
            settings = tmp;
            this.serializeSetting();
        }
        public void deSerializeSetting()
        {
            this.settings = (Hashtable)this.phpSer.Deserialize(this.settingsString);
        }
        public void serializeSetting()
        {
            this.SettingsString = this.phpSer.Serialize(this.settings);
        }
        /// <summary>
        /// This will merge the settings between the current settings and the ones given to this function.
        /// </summary>
        /// <param name="merge">Settings to merge in.</param>
        public void mergeSettings(Hashtable merge)
        {
            foreach (DictionaryEntry classItem in this.settings)
            {
                Hashtable varItem = (Hashtable)classItem.Value;
                foreach (DictionaryEntry var in varItem)
                {
                    ColumnAttribute attr = (ColumnAttribute)var.Value;
                    ColumnAttribute mergeAttr = null;
                    try
                    {
                        mergeAttr = (ColumnAttribute)((Hashtable)merge[classItem.Key])[var.Key];
                    }
                    catch
                    {
                    }
                    if (mergeAttr != null)
                    {
                        attr.ColumnName = mergeAttr.ColumnName;
                        attr.ColumnOrder = mergeAttr.ColumnOrder;
                        if (attr.VisableAllowed)
                            attr.VisableAllowed = mergeAttr.VisableAllowed;
                        attr.VisableDef = mergeAttr.VisableDef;
                    }
                }
            }
        }
    }
    /// <summary>
    /// Data that is above and beyond the default OFX account info data.  This includes a username,
    /// password, and the guid of the online account it connects to.
    /// </summary>
    public class AccountInfo : OFXACCOUNTINFO
    {
        protected string accountName;
        protected string accoutInfoID;
        protected string username;
        protected string password;
        protected string guid;
        protected string lastUsedDate;

        [ColumnAttribute(false, "Account Username", true, typeof(string))]
        public string Username
        {
            get { return username; }
            set { username = value; }
        }
        [ColumnAttribute(false, "Account Password", true, typeof(string))]
        public string Password
        {
            get { return password; }
            set { password = value; }
        }
        [ColumnAttribute(false, "", false, typeof(string))]
        public string AccoutInfoID
        {
            get { return accoutInfoID; }
            set { accoutInfoID = value; }
        }
        [ColumnAttribute(false, "", false, typeof(string))]
        public string GUID
        {
            get { return guid; }
            set { guid = value; }
        }
        [ColumnAttribute(true, "Account Name", true, typeof(string))]
        public string AccountName
        {
            get { return accountName; }
            set { accountName = value; }
        }
        [ColumnAttribute(false, "", false, typeof(string))]
        public string LastUsedDate
        {
            get { return lastUsedDate; }
            set { lastUsedDate = value; }
        }
        public AccountInfo()
        {
            accoutInfoID = "0";
            lastUsedDate = DateTime.Now.Subtract(TimeSpan.FromDays(365.0)).ToString("MM/dd/yyyy");
        }
        public void SetAccountID(string id)
        {
            accoutInfoID = id;
        }
    }
    /// <summary>
    /// Not used at this moment, but I hope to use it later
    /// </summary>
    public class ReoccuringTransactions
    {
        protected string defaultAmount;
        protected string reoccuringDay;
        protected bool isReoccuring;
        [ColumnAttribute(false, "", false, typeof(bool))]
        public bool IsReoccuring
        {
            get { return isReoccuring; }
            set { isReoccuring = value; }
        }
        [ColumnAttribute(false, "Reoccuring Day", true, typeof(int))]
        public string ReoccuringDay
        {
            get { return reoccuringDay; }
            set { reoccuringDay = value; }
        }
        [ColumnAttribute(false, "Default Amount", true, typeof(decimal))]
        public string DefaultAmount
        {
            get { return defaultAmount; }
            set { defaultAmount = value; }
        }
    }
    /// <summary>
    /// Transaction Information that goes above and beyond the standard OFX transactions.
    /// </summary>
    public class TransactionInfo : OFXSTMTTRN
    {
        protected string category;
        protected string month;
        protected string dateDue;
        protected ReoccuringTransactions reoccuringTrans;
        public ReoccuringTransactions ReoccuringTransaction
        {
            get { return reoccuringTrans; }
            set { reoccuringTrans = value; }
        }
        [ColumnAttribute(true, "Month", true, typeof(string))]
        public string Month
        {
            get { return month; }
            set { month = value; }
        }
        [ColumnAttribute(true, "Date Due", true, typeof(DateTime))]
        public string DateDue
        {
            get { return dateDue; }
            set { dateDue = value; }
        }
        [ColumnAttribute(true, "Category", true, typeof(string))]
        public string Category
        {
            get { return category; }
            set { category = value; }
        }
    }
    /*
    /// <summary>
    /// This isnt really useful anymore.  It will be removed in V0.5
    /// </summary>
    public class AccountDataV0_3
    {
        protected AccountInfo accountInfo;
        protected TransactionInfo transaction;
        protected ArrayList transactionList;

        public TransactionInfo Transaction
        {
            get { return transaction; }
            set { transaction = value; }
        }
        public AccountInfo AccountInfo
        {
            get { return accountInfo; }
            set { accountInfo = value; }
        }
        public ArrayList TransactionList
        {
            get { return transactionList; }
            set { transactionList = value; }
        }
        public AccountDataV0_3()
        {
            this.transactionList = new ArrayList();
            this.transaction = new TransactionInfo();
            this.accountInfo = new AccountInfo();            
        }
        public AccountDataV0_3(AccountDataV0_2 oldVersion, string accountName, AccountDataV0_4 sqlDB)
        {            
            this.transactionList = new ArrayList();
            this.accountInfo = new AccountInfo();
            ArrayList acctData = (ArrayList)oldVersion.AccountList[accountName];
            this.accountInfo.AccountName = accountName;
            this.accountInfo.SetAccountID(sqlDB.findLast(this.accountInfo));

            foreach (BasicAccountDataV0_2 tmpData in acctData)
            {
                TransactionInfo tmpTransaction = new TransactionInfo();
                tmpTransaction.Category = tmpData.Category;
                tmpTransaction.Amount = tmpData.Amount;
                tmpTransaction.PostedDate = tmpData.Date;
                tmpTransaction.Month = tmpData.Month;
                tmpTransaction.PayeeInfo = new OFXPAYEE();
                tmpTransaction.PayeeInfo.IsPayeeField = false;
                tmpTransaction.PayeeInfo.Name = tmpData.Name;
                tmpTransaction.AccountInfoID = this.accountInfo.AccoutInfoID;
                tmpTransaction.ReoccuringTransaction = new ReoccuringTransactions();
                tmpTransaction.ReoccuringTransaction.IsReoccuring = false;
                if (tmpTransaction.Amount.IndexOf("-") != -1)
                    tmpTransaction.Type = TransactionType.DEBIT;
                else tmpTransaction.Type = TransactionType.CREDIT;
                switch (tmpData.ThisStyle)
                {
                    case TemplateStyle.BankAccounts:
                        {
                            BankAccountDataV0_2 tmp = (BankAccountDataV0_2)tmpData;
                            tmpTransaction.CheckNumber = tmp.CheckNum;
                            this.AccountInfo.Type = AccountType.BANK;
                            break;
                        }
                    case TemplateStyle.Bills:
                        {
                            BillAccountDataV0_2 tmp = (BillAccountDataV0_2)tmpData;
                            tmpTransaction.CheckNumber = tmp.CheckNum;
                            tmpTransaction.ReoccuringTransaction.DefaultAmount = tmp.Amount;
                            tmpTransaction.DateDue = tmp.DateDue;
                            DateTime tmpDT = DateTime.MinValue;
                            DateTime.TryParse(tmp.DateDue, out tmpDT);
                            if (tmpDT != DateTime.MinValue)
                                tmpTransaction.ReoccuringTransaction.ReoccuringDay = tmpDT.Day.ToString();
                            this.AccountInfo.Type = AccountType.AP;
                            break;
                        }
                    case TemplateStyle.CreditCardAccounts:
                        {
                            CreditCardAccountDataV0_2 tmp = (CreditCardAccountDataV0_2)tmpData;
                            this.AccountInfo.Type = AccountType.CC;
                            if (tmpTransaction.Category != null && tmpTransaction.Category.IndexOf("Payment", 0, tmpTransaction.Category.Length, StringComparison.OrdinalIgnoreCase) != -1)
                            {
                                tmpTransaction.Type = TransactionType.CREDIT;
                            }
                            else tmpTransaction.Type = TransactionType.DEBIT;
                            break;
                        }
                    case TemplateStyle.Income:
                        {
                            IncomeAccountDataV0_2 tmp = (IncomeAccountDataV0_2)tmpData;
                            this.AccountInfo.Type = AccountType.AR;
                            break;
                        }
                    case TemplateStyle.ALL:
                    case TemplateStyle.Default:
                        {
                            this.AccountInfo.Type = AccountType.NA;
                            break;
                        }
                }
                sqlDB.addDataToDB(tmpTransaction);
            }
            sqlDB.addDataToDB(this.accountInfo);
            DataSet test = sqlDB.returnAll(new TransactionInfo());
        }
    }
     * */
    /// <summary>
    /// This is the newest version of the AccountData.  This is the 4th version.  It is based off of the SQL 
    /// data set.
    /// </summary>
    public class AccountDataV0_4 : BaseSQL
    {
        private ArrayList undoCmdList = new ArrayList();
        private ArrayList redoCmdList = new ArrayList();
        private Settings mySettings = new Settings();
        private Boolean transactionEnabled = false;
        private Int32 transactionID = 0;
        private Boolean isRedoCmd = false;
        protected bool isConnectionOpen = false;

        public Settings theSettings
        {
            get { return mySettings; }
        }
        public bool IsConnOpen
        {
            get { return isConnectionOpen; }
        }
        public AccountDataV0_4(string fileName) : base(fileName)
        {            
            if (this.sqlCon != null)
                isConnectionOpen = true;
            this.setupConnection();
            createSettings();
        }
        /// <summary>
        /// If I allowed a password, this would be the way to set it....too bad I dont use it
        /// </summary>
        /// <param name="fileName">SQL filename</param>
        /// <param name="password">SQL password</param>
        public AccountDataV0_4(string fileName, byte[] password)
            : base(fileName)
        {
            if (this.sqlCon != null)
                isConnectionOpen = true;
            this.setupConnection();
        }
        public void saveSettings(Hashtable settings)
        {
            this.mySettings.setSettings(settings);
            this.addDataToDB(this.mySettings, this.mySettings.getID());
        }
        /// <summary>
        /// Create the default settings and merge into the current filename.
        /// </summary>
        private void createSettings()
        {
            Hashtable tmp = new Hashtable();
            tmp.Add("TransactionInfo", ObjectStringClass.getColumnAttributes(new TransactionInfo()));
            tmp.Add("AccountInfo", ObjectStringClass.getColumnAttributes(new AccountInfo()));
            mySettings.setSettings(tmp);
            DataSet ds = this.returnAll(new Settings());
            if (ds.Tables[0].Rows.Count > 0)
            {
                Serializer phpSer = new Serializer();
                string str = ds.Tables[0].Rows[0]["Settings_SettingsString"].ToString();
                string setString = Encoding.ASCII.GetString(Convert.FromBase64String(str));
                Hashtable databaseSettings = (Hashtable)phpSer.Deserialize(setString);
                this.mySettings.mergeSettings(databaseSettings);
                this.mySettings.setID(ds.Tables[0].Rows[0]["ID"].ToString());
            }
            else
            {
                this.mySettings.setID(this.findLast(this.mySettings));
                this.addDataToDB(this.mySettings);                
            }
            
        }
        /// <summary>
        /// This creates all the tables if they dont already exist.
        /// </summary>
        private void setupConnection()
        {
            if (sqlCon != null)
            {
                string sqlText;
                string varibles = "";
                string holder = "";
                sqlCmd = new SQLiteCommand("", sqlCon);
                sqlText = "CREATE TEMP TABLE undoLog (ID INTEGER PRIMARY KEY AUTOINCREMENT, sqlText TEXT)";
                sqlCmd.CommandText = sqlText;
                sqlCmd.ExecuteNonQuery();                
                holder = ObjectStringClass.objectMemberNames(new AccountInfo());
                varibles = string.Join(" TEXT, ", holder.Split('|'));
                varibles += " TEXT, ID INTEGER PRIMARY KEY AUTOINCREMENT";
                sqlText = "CREATE TABLE IF NOT EXISTS AccountInfo (" + varibles + ")";
                sqlCmd.CommandText = sqlText;
                sqlCmd.ExecuteNonQuery();
                setupUndo(new string[] { "AccountInfo", holder + "|ID" });
                holder = ObjectStringClass.objectMemberNames(new TransactionInfo());
                varibles = string.Join(" TEXT, ", holder.Split('|'));
                varibles += " TEXT, ID INTEGER PRIMARY KEY AUTOINCREMENT";
                sqlText = "CREATE TABLE IF NOT EXISTS TransactionInfo (" + varibles + ")";
                sqlCmd.CommandText = sqlText;
                sqlCmd.ExecuteNonQuery();
                setupUndo(new string[] { "TransactionInfo", holder + "|ID" });
                holder = ObjectStringClass.objectMemberNames(new Version());
                varibles = string.Join(" TEXT, ", holder.Split('|'));
                varibles += " TEXT, ID INTEGER PRIMARY KEY AUTOINCREMENT";
                sqlText = "CREATE TABLE IF NOT EXISTS Version (" + varibles + ")";
                sqlCmd.CommandText = sqlText;
                sqlCmd.ExecuteNonQuery();
                holder = ObjectStringClass.objectMemberNames(new Settings());
                varibles = string.Join(" TEXT, ", holder.Split('|'));
                varibles += " TEXT, ID INTEGER PRIMARY KEY AUTOINCREMENT";
                sqlText = "CREATE TABLE IF NOT EXISTS Settings (" + varibles + ")";
                sqlCmd.CommandText = sqlText;
                sqlCmd.ExecuteNonQuery();
                setupUndo(new string[] { "Settings", holder + "|ID" });
                this.addDataToDB(new Version());
            }
        }
        /// <summary>
        /// Self explainitary
        /// </summary>
        /// <param name="tables">The tables to setup the undo for</param>
        public void setupUndo(string[] tables)
        {
            string tmp = tables[0];
            string varibles = tables[1];
            int i = 0;
            string sqlText;
            sqlCmd = new SQLiteCommand("", sqlCon);
            sqlText = "CREATE TEMP TRIGGER _" + tmp + "_it AFTER INSERT ON " + tmp + " BEGIN INSERT INTO undoLog VALUES(NULL,'DELETE FROM " + tmp + " WHERE rowid='||new.rowid);  END;";
            sqlCmd.CommandText = sqlText;
            sqlCmd.ExecuteNonQuery();
            sqlText = "CREATE TEMP TRIGGER _" + tmp + "_ut AFTER UPDATE ON " + tmp + " BEGIN INSERT INTO undoLog VALUES(NULL,'UPDATE " + tmp + " SET ";

            foreach (string variable in varibles.Split('|'))
            {
                if (i > 0)
                    sqlText += ",";
                sqlText += variable + "='||quote(old." + variable + ")||'";
                i++;
            }
            sqlText += " WHERE rowid='||old.rowid); END;";
            sqlCmd.CommandText = sqlText;
            sqlCmd.ExecuteNonQuery();
            sqlText = "CREATE TEMP TRIGGER _" + tmp + "_dt BEFORE DELETE ON " + tmp + " BEGIN INSERT INTO undoLog VALUES(NULL,'INSERT INTO " + tmp + "(rowid,";
            i = 0;
            foreach (string variable in varibles.Split('|'))
            {
                if (i > 0)
                    sqlText += ",";
                sqlText += variable;
                i++;
            }
            sqlText += ") VALUES('||old.rowid||',";
            i = 0;
            foreach (string variable in varibles.Split('|'))
            {
                if (i > 0)
                    sqlText += ",";
                sqlText += "'||quote(old." + variable + ")||'";
                i++;
            }

            sqlText += ")');END;";
            sqlCmd.CommandText = sqlText;
            sqlCmd.ExecuteNonQuery();
        }
        public override void beginTransaction(object obj)
        {
            base.beginTransaction(obj);
            this.transactionEnabled = true;
            if (isRedoCmd)
            {
                this.transactionID = redoCmdList.Count;
            }
            else this.transactionID = undoCmdList.Count;
        }
        public override void endTransaction(object obj)
        {
            base.endTransaction(obj);
            this.transactionEnabled = false;
            this.isRedoCmd = false;
        }
        public override bool addDataToDB(object obj)
        {
            bool rv = base.addDataToDB(obj);            
            if (rv)
            {
                sqlCmd = new SQLiteCommand(sqlCon);
                string sqlText = "SELECT coalesce(max(ID),0) from undoLog";                
                sqlCmd.CommandText = sqlText;
                SQLiteDataReader sqlReader = sqlCmd.ExecuteReader();
                this.isRedoCmd = false;
                while (sqlReader.Read())
                {
                    this.addcmdToUndoRedoList(sqlReader.GetValue(0), this.undoCmdList);
                }
            }
            return rv;
        }
        private void addcmdToUndoRedoList(object sqlValue, ArrayList cmdList)
        {
            if (this.transactionEnabled)
            {
                ArrayList transactionList;
                if (cmdList.Count == this.transactionID)
                {
                    transactionList = new ArrayList();
                    cmdList.Add(transactionList);
                }
                else transactionList = (ArrayList)cmdList[this.transactionID];
                transactionList.Add(sqlValue);
                cmdList[this.transactionID] = transactionList;
            }
            else cmdList.Add(sqlValue);
        }
        public override bool removeDataInDB(object obj, object ID)
        {
            bool rv = base.removeDataInDB(obj, ID);
            if (rv)
            {
                string sqlText = "SELECT coalesce(max(ID),0) from undoLog";
                sqlCmd = new SQLiteCommand(sqlCon);
                sqlCmd.CommandText = sqlText;
                SQLiteDataReader sqlReader = sqlCmd.ExecuteReader();
                this.isRedoCmd = false;
                while (sqlReader.Read())
                {
                    this.addcmdToUndoRedoList(sqlReader.GetValue(0), this.undoCmdList);
                }
            }
            return rv;
        }
        public override bool addDataToDB(object obj, object ID)
        {
            bool rv = base.addDataToDB(obj, ID);            
            if (rv)
            {
                string sqlText = "SELECT coalesce(max(ID),0) from undoLog";
                sqlCmd = new SQLiteCommand(sqlCon);
                sqlCmd.CommandText = sqlText;
                SQLiteDataReader sqlReader = sqlCmd.ExecuteReader();
                this.isRedoCmd = false;
                while (sqlReader.Read())
                {
                    this.addcmdToUndoRedoList(sqlReader.GetValue(0), this.undoCmdList);
                }
            }
            return rv;
        }
        public void undoCmd(object obj)
        {
            if (undoCmdList.Count > 0)
            {
                string sqlText = "";
                object undoCMD = undoCmdList[undoCmdList.Count - 1];
                ArrayList cmdList;
                Boolean transactionalDB = false;
                this.isRedoCmd = true;
                if (undoCMD is ArrayList)
                {
                    cmdList = (ArrayList)undoCMD;
                    this.beginTransaction(obj);
                    transactionalDB = true;
                }
                else
                {
                    cmdList = new ArrayList();
                    cmdList.Add(undoCMD);
                }
                foreach (object item in cmdList)
                {
                    sqlText = "SELECT sqlText from undoLog where ID = " + item.ToString();
                    sqlCmd = new SQLiteCommand(sqlText, sqlCon);
                    SQLiteDataReader sqlReader = sqlCmd.ExecuteReader();
                    while (sqlReader.Read())
                    {
                        SQLiteCommand sqlCmd2 = new SQLiteCommand("", sqlCon);
                        sqlText = sqlReader.GetValue(0).ToString();
                        sqlCmd2.CommandText = sqlText;
                        sqlCmd2.ExecuteNonQuery();
                        sqlText = "SELECT coalesce(max(ID),0) from undoLog";
                        sqlCmd2.CommandText = sqlText;
                        SQLiteDataReader sqlReader2 = sqlCmd2.ExecuteReader();
                        while (sqlReader2.Read())
                        {
                            this.addcmdToUndoRedoList(sqlReader2.GetValue(0), this.redoCmdList);                            
                        }
                    }                    
                }
                this.undoCmdList.RemoveAt(this.undoCmdList.Count - 1);
                if (transactionalDB)
                    this.endTransaction(obj);
            }
        }
        public void redoCmd(object obj)
        {
            if (redoCmdList.Count > 0)
            {
                string sqlText = "";
                object redoCMD = this.redoCmdList[this.redoCmdList.Count - 1];
                ArrayList cmdList;
                Boolean transactionalDB = false;
                this.isRedoCmd = false;
                if (redoCMD is ArrayList)
                {
                    cmdList = (ArrayList)redoCMD;
                    this.beginTransaction(obj);
                    transactionalDB = true;
                }
                else
                {
                    cmdList = new ArrayList();
                    cmdList.Add(redoCMD);
                }
                foreach (object item in cmdList)
                {
                    sqlText = "SELECT sqlText from undoLog where ID = " + item.ToString();
                    sqlCmd = new SQLiteCommand(sqlText, sqlCon);
                    SQLiteDataReader sqlReader = sqlCmd.ExecuteReader();
                    while (sqlReader.Read())
                    {
                        SQLiteCommand sqlCmd2 = new SQLiteCommand("", sqlCon);
                        sqlText = sqlReader.GetValue(0).ToString();
                        sqlCmd2.CommandText = sqlText;
                        sqlCmd2.ExecuteNonQuery();
                        sqlText = "SELECT coalesce(max(ID),0) from undoLog";
                        sqlCmd2.CommandText = sqlText;
                        SQLiteDataReader sqlReader2 = sqlCmd2.ExecuteReader();
                        while (sqlReader2.Read())
                        {
                            this.addcmdToUndoRedoList(sqlReader2.GetValue(0), this.undoCmdList);
                        }
                    }
                }
                if (transactionalDB)
                    this.endTransaction(obj);
                this.redoCmdList.RemoveAt(this.redoCmdList.Count - 1);
            }
        }

        public bool saveDB(FileInfo fileName)
        {
            bool saveOk = false;
            string sqlText;
            string tmpDBname;
            try
            {
                SQLiteConnection tmpsqlCon = new SQLiteConnection("Data Source=" + fileName.FullName);
                tmpsqlCon.Open();
                tmpsqlCon.Close();
                sqlText = "SELECT name FROM sqlite_master where type = 'table'";
                tmpDBname = fileName.Name.Split('.')[0];
                sqlCmd = new SQLiteCommand(sqlText, sqlCon);
                sqlCmd.CommandText = "ATTACH DATABASE '" + fileName.Name + ".bak' as '" + tmpDBname + "'";
                sqlCmd.ExecuteNonQuery();
                SQLiteCommand tmpSQLcmd = new SQLiteCommand(sqlText, sqlCon);
                sqlCmd.CommandText = sqlText;
                SQLiteDataReader sqlReader = sqlCmd.ExecuteReader();
                while (sqlReader.Read())
                {
                    try
                    {
                        string tableName = sqlReader.GetValue(0).ToString();
                        sqlText = "CREATE TABLE '" + tmpDBname + "'." + tableName + " as SELECT * from " + tableName;
                        tmpSQLcmd.CommandText = sqlText;
                        int rows = tmpSQLcmd.ExecuteNonQuery();
                    }
                    catch
                    {
                    }
                }
                sqlReader.Close();
                sqlCmd.CommandText = "DETACH  DATABASE '" + tmpDBname + "'";
                sqlCmd.ExecuteNonQuery();
                saveOk = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Crap, I couldnt save your file.  Send this to the bug report: " + ex.Message);
            }
            finally
            {
                if (saveOk)
                {
                    FileInfo backupFile = new FileInfo(fileName.Name + ".bak");
                    FileInfo saveFile = fileName;
                    if (saveFile.Exists)
                    {
                        saveFile.Delete();
                    }
                    backupFile.MoveTo(fileName.FullName);
                }
            }
            return saveOk;

        }
        public string upgradeDB(FileInfo filename)
        {
            string copyBackupString = filename.FullName + "." + DateTime.Now.Ticks.ToString();
            FileInfo copyBackup = new FileInfo(filename.FullName);
            copyBackup.CopyTo(copyBackupString);
            SQLiteConnection upgradeCon = new SQLiteConnection("Data Source=" + copyBackupString);
            SQLiteCommand upgradeCmd = new SQLiteCommand("", upgradeCon);
            string SQLtext = "";
            SQLtext = "SELECT * FROM Version";
            upgradeCmd.CommandText = SQLtext;
            upgradeCon.Open();
            SQLiteDataReader upgradeReader = upgradeCmd.ExecuteReader();
            if (upgradeReader.HasRows)
            {
                while (upgradeReader.Read())
                {
                    object obj = upgradeReader.GetValue(0);
                    switch (obj.ToString())
                    {
                        case "0.4.2": break;
                        default: break;
                    }
                }
            }
            else 
            //Before Version 0.4.2 we didnt store the version number so we must 
            //assume the code came from version 0.4
            {
                upgradeReader.Close();
                upgradeReader.Dispose();
                SQLtext = "ALTER TABLE AccountInfo ADD COLUMN AccountInfo_LastUsedDate";
                upgradeCmd.CommandText = SQLtext;
                upgradeCmd.ExecuteNonQuery();
            }
            upgradeCon.Close();
            upgradeCmd.Dispose();
            return copyBackupString;
        }
        public bool loadDB(FileInfo fileName)
        {
            bool openOK = false;
            string backupString = "";
            try
            {
                backupString = this.upgradeDB(fileName);
                sqlCon.Close();
                sqlCon.Open();
                this.setupConnection();
                string sqlText = "SELECT name FROM sqlite_master where type = 'table'";
                string tmpDBname = backupString.Split('.')[0];
                sqlCmd = new SQLiteCommand(sqlText, sqlCon);
                sqlCmd.CommandText = "ATTACH DATABASE '" + backupString + "' as '" + tmpDBname + "'";
                sqlCmd.ExecuteNonQuery();                
                sqlText = "SELECT name FROM '" + tmpDBname + "'.sqlite_master where type = 'table'";
                sqlCmd.CommandText = sqlText;
                SQLiteDataReader sqlReader = sqlCmd.ExecuteReader();
                while (sqlReader.Read())
                {
                    string tableName = sqlReader.GetValue(0).ToString();
                    object obj = ObjectStringClass.findObjectByName(tableName);
                    string varstring = string.Join(" , ", ObjectStringClass.objectMemberNames(obj).Split('|')) + " , ID";
                    if (tableName == "Version")
                        sqlText = "INSERT OR REPLACE INTO " + tableName + " (" + varstring + ") SELECT " + varstring + " from '" + tmpDBname + "'." + tableName;
                    else
                    sqlText = "INSERT INTO " + tableName + " (" + varstring + ") SELECT " + varstring +" from '" + tmpDBname + "'." + tableName;
                    SQLiteCommand tmpSQLcmd = new SQLiteCommand(sqlText, sqlCon);
                    tmpSQLcmd.CommandText = sqlText;
                    int rows = tmpSQLcmd.ExecuteNonQuery();
                }
                sqlReader.Close();
                sqlReader.Dispose();
                sqlCmd.CommandText = "DETACH  DATABASE '" + tmpDBname + "'";
                sqlCmd.ExecuteNonQuery();
                openOK = true;
            }
            catch (FileNotFoundException)
            {
                MessageBox.Show("I'm sorry, I cant find your file");
            }
            catch(Exception ex)
            {
                MessageBox.Show("Crap, I couldnt open your file, please put this into the bug report: " + ex.Message);
            }
            createSettings();
            this.undoCmdList.Clear();
            this.redoCmdList.Clear();
            this.isRedoCmd = false;
            if (backupString != "")
            {
                FileInfo deleteInfo = new FileInfo(backupString);
                deleteInfo.Delete();
            }
            return openOK;
        }
        public void Close()
        {
            sqlCon.Close();                        
        }
    }

    /// <summary>
    /// I almost forgot about this object.  It is the object that stores all the information used by the Data Grid
    /// Stored in the Tag.
    /// </summary>
    public class TagObject
    {
        public string SQLtext;
        public Hashtable SQLobjs;
        public bool comboBoxEditable;
        public decimal total = 0.0m;
        public string variableName;
        public object lastUsed;
    }
}
